package cn.life.business.apply.dao.impl;

import cn.life.business.apply.dao.ApplyDao;
import cn.life.business.apply.entity.po.ApplyDetailPo;
import cn.life.business.apply.entity.po.ApplyPo;
import cn.life.business.apply.entity.vo.ApplyVo;
import cn.life.common.entity.po.MessagePo;
import cn.life.common.entity.vo.Pagination;
import cn.life.common.support.jdbc.AbstractSupportDao;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 王振宇
 * @Description:
 * @Date 2018-09-18 10:59:28
 */
@Repository
public class ApplyDaoImpl extends AbstractSupportDao implements ApplyDao {


    @Override
    public Long addApply(ApplyPo applyPo) {
        Long id=queryTableNextId("apply_info","apply_id");
        String sql= "INSERT INTO apply_info(apply_id,apply_no,apply_name,apply_company_name,apply_type,apply_person,apply_addr,apply_phone,apply_street,opr_user_id,opr_user_name,opr_date) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
        return update(sql,id,applyPo.getApplyNo(),applyPo.getApplyName(),applyPo.getApplyCompanyName(),
                applyPo.getApplyType(),applyPo.getApplyPerson(),applyPo.getApplyAddr(),applyPo.getApplyPhone(),applyPo.getApplyStreet(),applyPo.getOprUserId(),applyPo.getOprUserName(),applyPo.getOprDate()) == 1 ? id : null;
    }

    @Override
    public Pagination<ApplyVo> queryList(ApplyVo applyVo) {
        StringBuffer stringBuffer=new StringBuffer("select distinct ap.*, au.audit_state  from apply_info ap left join audit_info au on ap.apply_no = au.apply_no where 1=1");
        if ((!"".equals(applyVo.getApplyNo())&&applyVo.getApplyNo()!=null)||(!"".equals(applyVo.getApplyName())&&applyVo.getApplyName()!=null)||(!"".equals(applyVo.getApplyCompanyName())&&applyVo.getApplyCompanyName()!=null)){
            stringBuffer.append(" and (ap.apply_no like'%"+applyVo.getApplyNo()+"%' or ap.apply_name like '%"+applyVo.getApplyName()+"%' or ap.apply_company_name like '%"+applyVo.getApplyCompanyName()+"%')  ");
        }
        if (!"".equals(applyVo.getBeginDate())&&applyVo.getBeginDate()!=null){
            stringBuffer.append(" and  '" + getSqlDate(applyVo.getBeginDate()) + "' <=ap.opr_date");
        }
        if (!"".equals(applyVo.getOverDate()) && applyVo.getOverDate()!=null){
            stringBuffer.append(" and  '" +  getEndDate(applyVo.getOverDate()) + "' >=ap.opr_date");
        }
        if (applyVo.getUserState() == 1){
            stringBuffer.append(" and  ap.opr_user_name like'%"+applyVo.getQueryUserName()+"%'");
        }
        stringBuffer.append(" order by audit_state asc , opr_date desc");
        return queryForListByPageEx(stringBuffer.toString(),ApplyVo.class,applyVo.getPageIndex(),applyVo.getPageSize());
    }

    @Override
    public ApplyVo querySingle(ApplyVo applyVo) {
        String sql="select a.*,ad.img_url,au.audit_state from apply_info a LEFT JOIN apply_detail ad ON a.apply_id=ad.apply_id  INNER JOIN audit_info au on au.apply_id=a.apply_id where a.apply_id=?";
        return queryForObject(sql,ApplyVo.class,applyVo.getApplyId());
    }

    @Override
    public int updateApply(ApplyPo applyPo) {
        String sql="update apply_info set apply_no=?,apply_name=?,apply_company_name=?,apply_type=?," +
                "apply_person=?,apply_addr=?,apply_phone=?,apply_street=? where apply_id=?";
        return update(sql,applyPo.getApplyNo(),applyPo.getApplyName(),applyPo.getApplyCompanyName(),applyPo.getApplyType(),
                applyPo.getApplyPerson(),applyPo.getApplyAddr(),applyPo.getApplyPhone(),applyPo.getApplyStreet(),applyPo.getApplyId());
    }

    @Override
    public int deleteApply(ApplyPo applyPo) {
        String sql="delete from apply_info where apply_id=?";
        return update(sql,applyPo.getApplyId());
    }

    @Override
    public int deleteApplyImg(ApplyPo applyPo) {
        String sql="delete from apply_detail where apply_id=?";
        return update(sql,applyPo.getApplyId());
    }

    @Override
    public List<ApplyVo> Excel(ApplyVo applyVo) {
       /* StringBuffer stringBuffer=new StringBuffer("SELECT ap.*,apd.img_url,adi.audit_state from apply_info ap INNER JOIN apply_detail apd ON ap.apply_id=apd.apply_id INNER JOIN audit_info adi ON ap.apply_id=adi.apply_id WHERE 1=1 ");
        if ((!"".equals(applyVo.getApplyNo())&&applyVo.getApplyNo()!=null)||(!"".equals(applyVo.getApplyName())&&applyVo.getApplyName()!=null)){
            stringBuffer.append(" and (ap.apply_no like'%"+applyVo.getApplyNo()+"%' or ap.apply_name like '%"+applyVo.getApplyName()+"%') ");
        }
        if (!"".equals(applyVo.getBeginDate())&&applyVo.getBeginDate()!=null){
            stringBuffer.append(" and  '" + getSqlDate(applyVo.getBeginDate()) + "' <=ap.opr_date");
        }
        if (!"".equals(applyVo.getOverDate()) && applyVo.getOverDate()!=null){
            stringBuffer.append(" and  '" +  getEndDate(applyVo.getOverDate()) + "' >=ap.opr_date");
        }
        stringBuffer.append(" ORDER BY audit_state ASC , opr_date DESC");
        return queryForList(stringBuffer.toString(),ApplyVo.class);*/
        StringBuffer stringBuffer=new StringBuffer("select distinct a.*,b.audit_state from apply_info a,audit_info b where a.apply_id = b.apply_id ");
        if ((!"".equals(applyVo.getApplyNo())&&applyVo.getApplyNo()!=null)||(!"".equals(applyVo.getApplyName())&&applyVo.getApplyName()!=null)){
            stringBuffer.append(" and (a.apply_no like'%"+applyVo.getApplyNo()+"%' or a.apply_name like '%"+applyVo.getApplyName()+"%') ");
        }
        if (!"".equals(applyVo.getBeginDate())&&applyVo.getBeginDate()!=null){
            stringBuffer.append(" and  '" + getSqlDate(applyVo.getBeginDate()) + "' <=a.opr_date");
        }
        if (!"".equals(applyVo.getOverDate()) && applyVo.getOverDate()!=null){
            stringBuffer.append(" and  '" +  getEndDate(applyVo.getOverDate()) + "' >=a.opr_date");
        }
        stringBuffer.append(" ORDER BY b.audit_state ASC , a.opr_date DESC");
        return queryForList(stringBuffer.toString(),ApplyVo.class);
    }

    @Override
    public Integer queryAdvertisement(ApplyPo applyPo) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd ");
        String nowDate = format.format(new Date());
        String sql="SELECT (COUNT(*)+1) FROM apply_info where apply_type=? AND opr_date like '" + nowDate + "%'";
        return queryForObject(sql,Integer.class,applyPo.getApplyType());
    }

    @Override
    public void addApplyDetailList(List<ApplyDetailPo> detailPos) {
        String sql = "insert into apply_detail(apply_id,img_url,img_type,img_sort) values(?,?,?,?)";
        List<Object[]> list = new ArrayList<>();
        int i=0;
        for(ApplyDetailPo detailPo : detailPos){
            Object[] objects = new Object[4];
            objects[0]=detailPo.getApplyId();
            objects[1]=detailPo.getImgUrl();
            objects[2]=detailPo.getImgType();
            objects[3]=i;
            list.add(objects);
            i++;
        }
        batchUpdate(sql,list);
    }

    @Override
    public ApplyVo queryApplyInfo(ApplyVo applyVo) {
        String sql = "SELECT distinct a.*,au.audit_state,au.audit_desc,au.certified_img_url as certifiedImgUrlSuc FROM apply_info a LEFT JOIN audit_info au ON au.apply_id = a.apply_id WHERE a.apply_id =?";
        return queryForObject(sql,ApplyVo.class,applyVo.getApplyId());
    }

    @Override
    public List<ApplyDetailPo> queryAppliDetailList(ApplyVo applyVo) {
        String sql = "select * from apply_detail where apply_id = ? order by img_type asc,img_sort asc";
        return queryForList(sql,ApplyDetailPo.class,applyVo.getApplyId());
    }

    @Override
    public void updateApplyCertifiedImgUrl(ApplyPo applyPo) {
        String sql = "update apply_info set certified_img_url = ? where apply_id = ?";
        update(sql,applyPo.getCertifiedImgUrl(),applyPo.getApplyId());
    }

    @Override
    public void updateAuditState(ApplyPo applyPo) {
        String sql = "update audit_info set audit_state = null where apply_id = ?";
        update(sql,applyPo.getApplyId());
    }

    @Override
    public int addPhoneMessage(MessagePo messagePo) {
        String sql = "insert into phone_message(phone,code,begin_date,end_date) values(?,?,?,?)";
        return update(sql,messagePo.getPhone(),messagePo.getCode(),getNowDate(),getAfterDate(5));
    }

    @Override
    public boolean checkPhoneMessage(MessagePo messagePo) {
        String sql = "select count(1) from phone_message where phone = ? and code = ? and now() <= end_date";
        return queryForObject(sql,Integer.class,messagePo.getPhone(),messagePo.getCode()) > 0 ? true : false;
    }

    @Override
    public String getAuditPerPhone() {
        String sql = "select group_concat(user_phone) from user_info where state = 2";
        return queryForObject(sql,String.class);
    }


}
